package com.asmember;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import com.asmember.model.ASN;
import com.asmember.model.IP;
import com.asmember.model.Port;
import com.asmember.model.Search;

/**
 * @author Grzegorz Matyszczak<BR>
 *
 */
public class DBHelper {

	private static final String HOST = "stevie.heliohost.org";
	private static final String PORT = "3306";
	private static final String USER = "grzegmat_user";
	private static final String PASSWORD = "password123";
	private static final String DB_NAME = "grzegmat_asmember";
	
	public void insertConnectionIp(Map<String, List<String>> connectedHosts, List<String> ports, Search search){
		try {
			Connection conn = getConnection();
			
			StringBuilder builder = new StringBuilder("INSERT INTO `"+DB_NAME+"`.`connectionsIP`(ipId, port, searchId) VALUES ");
			
			
			for(String currentPort: ports){
				Port port = new Port(Integer.parseInt(currentPort));
				insertPortIfNotExists(conn, port);
				
				List<String> currentIpList = connectedHosts.get(currentPort);
				
				for(String currentIp: currentIpList)
					builder.append("('"+currentIp+"', '"+Integer.parseInt(currentPort)+"', '"+search.getId()+"'), ");
				
			}
				
			builder.delete(builder.length()-2, builder.length()-1);
			Statement statement = conn.createStatement();
			statement.executeUpdate(builder.toString());
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void insertSearch(Search search) {
		try {
			Connection conn = getConnection();
			
			//insert ASN to database if doesn't exist
			ASN asn = search.getAsn();
			insertASNIfNotExists(conn, asn);
			

			//insert new search to database
			String query = "INSERT INTO `"+DB_NAME+"`.`searches`(asnId, duration) VALUES ('"+asn.getId()+"', '"+search.getDuration()+"') ";
			Statement statement = conn.createStatement();
			statement.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
			ResultSet set = statement.getGeneratedKeys();
			if(set.first())
				search.setId(set.getInt(1));
			
			//insert IPs to database if don't exist
			List<IP> ips = search.getIps();
			for(IP ip : ips)
				insertIPIfNotExists(conn, ip);

			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	

	

	/**
	 * @param conn
	 * @param ip
	 * @throws SQLException 
	 */
	private void insertIPIfNotExists(Connection conn, IP ip) throws SQLException {
		String query = "SELECT * FROM `"+DB_NAME+"`.`ip` WHERE `address`='"+ip.getAddress()+"'";
		Statement statement = conn.createStatement();
		ResultSet result = statement.executeQuery(query);
		if(!result.first()) {
			query = "INSERT INTO `"+DB_NAME+"`.`ip`(address, asnId) VALUES ('"+ip.getAddress()+"', '"+ip.getAsn().getId()+"') ";
			statement.executeUpdate(query);
		}
	}


	/**
	 * @param conn 
	 * @param asn
	 * @return
	 * @throws SQLException 
	 */
	private void insertASNIfNotExists(Connection conn, ASN asn) throws SQLException {
		String query = "SELECT * FROM `"+DB_NAME+"`.`asn` WHERE `number`='"+asn.getNumber()+"'";
		Statement statement = conn.createStatement();
		ResultSet result = statement.executeQuery(query);
		if(!result.first()) {
			query = "INSERT INTO `"+DB_NAME+"`.`asn`(number) VALUES ('"+asn.getNumber()+"') ";
			statement.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
			ResultSet set = statement.getGeneratedKeys();
			if(set.first())
				asn.setId(set.getInt(1));
		}
		else
			asn.setId(result.getInt(1));
	}

	public Connection getConnection() throws SQLException {

		Connection conn = null;
		Properties connectionProps = new Properties();
		connectionProps.put("user", USER);
		connectionProps.put("password", PASSWORD);
		conn = DriverManager.getConnection("jdbc:mysql://" + HOST + ":" + PORT
				+ "/", connectionProps);
		return conn;
	}
	
	private void insertPortIfNotExists(Connection conn, Port port) throws SQLException {
		String query = "SELECT * FROM `"+DB_NAME+"`.`ports` WHERE `port`='"+port.getPort()+"'";
		Statement statement = conn.createStatement();
		ResultSet result = statement.executeQuery(query);
		if(!result.first()) {
			query = "INSERT INTO `"+DB_NAME+"`.`ports`(port) VALUES ('"+port.getPort()+"') ";
			statement.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
			ResultSet set = statement.getGeneratedKeys();
			if(set.first())
				port.setId(set.getInt(1));
		}
		else
			port.setId(result.getInt(1));
		}
	
}
